package com.example.cachedemo.mapper;

import com.example.cachedemo.service.dto.ClassInfo;
import com.example.cachedemo.service.dto.DepartmentInfo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

//考勤数据操作方法
@Mapper
public interface CheckMapper {
    /*根据部门编码获取部门对象
    @param depNO部门编码*/
    @Select("select * from niit_check_dep where dep_no=#{depNo}")
    DepartmentInfo getDepByDepNo(String depNo);

    /*定义根据班级名称查询班级数据方法
    @param className班级名称*/
    @Select("select * from niit_check_class where class_name=#{className}")
    @Result(column = "dep_no", property = "departmentInfo", one = @One(select =
            "getDepByDepNo", fetchType = FetchType.EAGER))
    ClassInfo getClassInfoByClassName(String className);

   /* 定义根据班级编码、学期查询班级平均出勤率方法
    @param classNo 班级编码
    @param termNo学期*/

    @Select("SELECT AVG(check_ratio) check_ratio FROM niit_check_main WHERE " +
            "class_no=#{classNo} AND term_no=#{termNo}")
    Float getClassRatioByClassNameAndTerm(@Param("classNo") String classNo,
                                          @Param("termNo") String termNo);

    /*定义根据班级编码、学期查询班级平均出勤率方法
    @param classNo 班级编码
    @param termNo学期*/

    @Select(" SELECT SUM(check_ratio) check_ratio FROM niit_check_main a , " +
            " niit_check_class b WHERE a.class_no=b.class_no AND b.dep_no=#{depNo} " +
            "AND term_no=#{termNo}")
    Float getDepRatioByDepNoAndTerm(@Param("depNo") String depNo,
                                    @Param("termNo") String termNo);


    @Select("SELECT SUM(check_kk) check_kk FROM niit_check_main WHERE " +
            "class_no=#{classNo} AND term_no=#{termNo}")
    Integer getClassAbsentByClassNameAndTerm(@Param("classNo") String classNo,
                                             @Param("termNo") String termNo);

     /*定义根据班级编码、学期查询班级平均出勤率方法
    @param classNo 班级编码
    @param termNo学期*/

    @Select(" SELECT SUM(check_kk) check_kk FROM niit_check_main a , " +
            " niit_check_class b WHERE a.class_no=b.class_no AND b.dep_no=#{depNo} " +
            "AND term_no=#{termNo}")
    Integer getDepAbsentByDepNoAndTerm(@Param("depNo") String depNo,
                                       @Param("termNo") String termNo);
}